Interval type value | Description |
---|---|
Number of firstDayOfWeek's between startDateTime and endDateTime | |
DateDiff returns a number of time intervals between two specified dates.
The following examples are applicable to both Basic and Crystal syntax:
Use DateDiff with the "d" or "y" interval type parameter to find the number of days between two dates:
DateDiff ("d", #10/7/1999#, #10/10/1999#)
Use DateDiff with the "yyyy" interval type parameter to find the number of years difference between two dates. This use of DateDiff is the same as finding the difference between the year of endDateTime and the year of startDateTime.
DateDiff ("yyyy", #10/7/1999#, #2/10/2005#)
DateDiff ("yyyy", #12/31/1999#, #1/1/2000#)
Returns 1 (a 1 year difference), even though there is only a 1 day difference between the dates.
DateDiff ("yyyy", #1/1/1999#, #12/31/1999#)
Returns 0 (a 0 year difference), even though there is a 364 day difference.
Suppose that for the above examples, the first date is the date that you bought a mutual fund, and the second date is the date you sold it. The mutual fund company must send you an annual report for every year in which you owned units in the fund. Thus, you would get 7, 2 and 1 annual reports respectively, in the above cases.
Use DateDiff with the "q" parameter to find the number of quarters (3 month periods) difference between two dates.
DateDiff ("q", #10/6/1999#, #5/20/2003#)
DateDiff ("q", #3/31/1999#, #4/1/1999#)
Returns 1. The two dates are in adjacent quarters.
DateDiff ("q", #1/1/1999#, #3/31/1999#)
Returns 0. The two dates are in the same quarter.
Suppose the mutual fund company in the "yyyy" example mailed out quarterly reports. Then it would need to mail out 15, 2 and 1 quarterly reports respectively, in the above cases.
Use DateDiff with the "m" parameter to find the number of months difference between two dates.
DateDiff ("m", #3/15/1999#, #7/13/1999#)
Use DateDiff with the "w" parameter to calculate the number of weeks between two dates. For example, if startDateTime is on a Tuesday, DateDiff counts the number of Tuesdays between startDateTime and endDateTime not including the initial Tuesday of startDateTime. Note however that it counts endDateTime if endDateTime is on a Tuesday.
DateDiff ("w", #10/19/1999#, #10/25/1999#)
DateDiff ("w", #10/19/1999#, #10/26/1999#)
Use DateDiff with the "ww" parameter to calculate the number of firstDayOfWeek's occurring between two dates. For the DateDiff function, the "ww" parameter is the only one that makes use of the firstDayOfWeek argument. It is ignored for all the other interval type parameters. For example, if firstDayOfWeek is crWednesday, it counts the number of Wednesday's between startDateTime and endDateTime. It does not count startDateTime even if startDateTime falls on a Wednesday, but it does count endDateTime if endDateTime falls on a Wednesday. For the examples, note that October 6, 13, 20 and 27 all fall on a Wednesday in 1999.
DateDiff ("ww", #10/5/1999#, #10/29/1999#, crWednesday)
DateDiff ("ww", #10/6/1999#, #10/29/1999#, crWednesday)
DateDiff ("ww", #10/5/1999#, #10/27/1999#, crWednesday)
For example, suppose you want to calculate the number of days between the order date and ship date, excluding Saturdays and Sundays:
formula = DateDiff("d", d1, d2) - _
DateDiff("ww", d1, d2, crSaturday) - _
DateDiff("ww", d1, d2, crSunday)
Local DateTimeVar d1 := {Orders.Order Date};
Local DateTimeVar d2 := {Orders.Ship Date};
DateDiff ("ww", d1, d2, crSaturday) -
DateDiff ("ww", d1, d2, crSunday)
This function is designed to work like the Visual Basic function of the same name.
Seagate Software IMG Holdings, Inc. http://www.seagatesoftware.com Support services: http://support.seagatesoftware.com |